<?php
/**
 * Excel管理类
 * comfiger
 * 2016-8-28
 **/
namespace Home\Model;
use Think\Model;

class ExcelModel extends Model{
	//定义表名称
	protected $tableName = 'users';
	
	/**
	 * 导出Excel
	 **/
	public function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
		
		import("Org.Util.PHPExcel");
		
        $objPHPExcel = new \PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
		// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
        } 
        // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
			}             
        }
        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
        exit;
    }
	
	/**
	 * 读取Excel
	 *
	 **/
	public function readExcel($file, $extension, $i=6){
		import("Org.Util.PHPExcel");
		import("Org.Util.PHPExcel.IOFactory.php");
		if( $extension =='xlsx'){
			$type = 'Excel2007';
		}else{
			$type = 'Excel5';
		}
		$reader = \PHPExcel_IOFactory::createReader($type); //设置以Excel5格式(Excel97-2003工作簿)
		
		$PHPExcel = $reader->load($file); // 载入excel文件
		$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		
		/** 循环读取每个单元格的数据 */
		for ($row = $i; $row <= $highestRow; $row++){//行数是以第1行开始
			for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
				//$dataset[] = $sheet->getCell($column.$row)->getValue(); getFont()
				$cell = $sheet->getCell($column.$row)->getValue();
				if(is_object($cell))  $cell= $cell->__toString(); //转化文本
				$dataset[$column] = $cell;
			}
			$array[$row] = $dataset;
		}
		
		return $array;
	}
}